package com.meng.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.meng.bean.User;

/**
 * 
 * @ClassName: UserDao
 * @Description: dao层
 * @author 孟凡岭
 */
@Repository
public class UserDao implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7256714813922427956L;

	@Resource
	private JdbcTemplate jdbcTemplate;

	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	private final String INSERT = "insert into user(userId,userName,createdAt) values(:UserId,:userName,:createdAt)";
	private final String UPDATE = "update user set userName = :userName,createdAt=:createdAt where userId=:UserId";
	private final String DELETE = "delete from user where userId=?";

	/**
	 * 
	 * @userName: create
	 * @Description: 创建
	 * @author 孟凡岭
	 * @param @param User
	 * @param @return
	 * @return int 返回类型
	 * @throws
	 */
	public int create(User User) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("UserId", User.getUserId());
		params.put("userName", User.getUserName());
		params.put("createdAt", User.getCreatedAt());
		// 返回主键
		KeyHolder keyholder = new GeneratedKeyHolder();
		jdbcTemplate.update(INSERT, params, keyholder);

		return keyholder.getKey().intValue();
	}

	/**
	 * 
	 * @userName: create2
	 * @Description: 创建方式2
	 * @author 孟凡岭
	 * @param @param User
	 * @param @return
	 * @return int 返回类型
	 * @throws
	 */
	public int create2(final User User) {
		final String sql = "insert into User(userName,createdAt) values(?,?)";
		KeyHolder keyholder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, User.getUserName());
				ps.setTimestamp(2, new Timestamp(User.getCreatedAt().getTime()));
				return ps;
			}
		}, keyholder);
		return keyholder.getKey().intValue();
	}

	/**
	 * 
	 * @userName: create3
	 * @Description: 创建方式3
	 * @author 孟凡岭
	 * @param @param User
	 * @param @return
	 * @return int 返回类型
	 * @throws
	 */
	public int create3(User User) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(User);
		KeyHolder keyholder = new GeneratedKeyHolder();
		namedParameterJdbcTemplate.update(INSERT, ps, keyholder);
		return keyholder.getKey().intValue();
	}

	/**
	 * 
	 * @userName: update
	 * @Description: 更新方法
	 * @author 孟凡岭
	 * @param @param User
	 * @return void 返回类型
	 * @throws
	 */
	public void update(User User) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("UserId", User.getUserId());
		params.put("userName", User.getUserName());
		params.put("createdAt", User.getCreatedAt());
		namedParameterJdbcTemplate.update(UPDATE, params);
	}

	/**
	 * 
	 * @userName: update2
	 * @Description: 更新方法2
	 * @author 孟凡岭
	 * @param @param User
	 * @return void 返回类型
	 * @throws
	 */
	public void update2(User User) {
		namedParameterJdbcTemplate.update(UPDATE,
				new BeanPropertySqlParameterSource(User));
	}

	/**
	 * 
	 * @userName: delete
	 * @Description: 删除方法
	 * @author 孟凡岭
	 * @param @param id
	 * @return void 返回类型
	 * @throws
	 */
	public void delete(Integer id) {
		jdbcTemplate.update(DELETE, id);
	}

	/***
	 * 
	 * @userName: findAll
	 * @Description: 查询全部
	 * @author 孟凡岭
	 * @param @return
	 * @return List<User> 返回类型
	 * @throws
	 */
	public List<User> findAll() {
		String sql = "select * from User";
		return jdbcTemplate.query(sql, new Object[] {},
				new BeanPropertyRowMapper<User>(User.class));
	}

	/**
	 * 
	 * @userName: getCount
	 * @Description: 统计
	 * @author 孟凡岭
	 * @param @return
	 * @return int 返回类型
	 * @throws
	 */
	public int getCount() {
		String sql = "select count(*) from User";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	/**
	 * 
	 * @userName: findById
	 * @Description: ID查询实体
	 * @author 孟凡岭
	 * @param @param id
	 * @param @return
	 * @return User 返回类型
	 * @throws
	 */
	public User findById(Integer id) {
		return jdbcTemplate.query("select * from User where userId=?",
				new Object[] { id },
				new BeanPropertyRowMapper<User>(User.class)).get(0);
	}

	/**
	 * 
	 * @userName: findById2
	 * @Description: ID查询实体2
	 * @author 孟凡岭
	 * @param @param id
	 * @param @return
	 * @return User 返回类型
	 * @throws
	 */
	public User findById2(Integer id) {
		return jdbcTemplate.query("select * from User where userId=?",
				new Object[] { id }, new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						User User = new User();
						User.setUserId(rs.getInt("userId"));
						User.setUserName(rs.getString("userName"));
						User.setCreatedAt(rs.getTimestamp("createdAt"));
						return User;
					}
				}).get(0);
	}
}
